/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.LoaiSanPham;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 *
 * @author Khôi
 */
public class LoaiSanPhamDao {

	public static ArrayList<LoaiSanPham> layDanhSach() {
		ArrayList<LoaiSanPham> ds = new ArrayList<LoaiSanPham>();
		String sql = "SELECT  MaLSP  , TenLSP   FROM  LoaiSanPham ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaLSP");
				String ten = rs.getString("TenLSP");

				LoaiSanPham tmp = new LoaiSanPham(ma, ten);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<LoaiSanPham> timKiemByTen(String tenLSP) {
		ArrayList<LoaiSanPham> ds = new ArrayList<LoaiSanPham>();

		String sql = "SELECT * FROM LoaiSanPham WHERE TenLSP LIKE N'%" + tenLSP + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaLSP");
				String ten = rs.getString("TenLSP");

				LoaiSanPham tmp = new LoaiSanPham(ma, ten);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static boolean them(LoaiSanPham tmp) {
		boolean kq = false;
		//String sql =  ";

		String s = String.format("INSERT INTO  LoaiSanPham  ( MaLSP  , TenLSP  ) VALUES( %d, N'%s')", tmp.getMaLSP(), tmp.getTenLSP());
		//s += sql;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(s);
		} catch (Exception ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean xoa(int MaLSP) {
		boolean kq = false;
		String str = "if not exists (select sp.MaLSP from  SanPham sp where sp.MaLSP = " + MaLSP;
		String sql = String.format(str + ") DELETE FROM LoaiSanPham WHERE MaLSP=%d", MaLSP);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean capNhat(LoaiSanPham tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE LoaiSanPham SET TenLSP = N'%s' WHERE  MaLSP = %d ", tmp.getTenLSP(), tmp.getMaLSP());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static LoaiSanPham layByMa(int maLSP) {

		LoaiSanPham tmp = null;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT * FROM LoaiSanPham WHERE MaLSP =" + maLSP;
			cn = new KetNoi();
			rs = cn.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaLSP");
				String ten = rs.getString("TenLSP");

				tmp = new LoaiSanPham(ma, ten);

			}
		} catch (SQLException ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}

	public int layMaMax() {
		int kq = 0;
		String sql = "Select Max(MaLSP) as max from LOAISANPHAM";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("max");
				//return ma;
				kq = ma;
			}
		} catch (Exception ex) {
			Logger.getLogger(LoaiSanPhamDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}

		return kq;
	}
}
